﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_FIM_PaymentCheques : IDAL_FIM_PaymentCheques
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        

        public D_FIM_PaymentCheques()
        {

        }

        public IList<M_FIM_PaymentCheques> GetFull(Nullable<int> pay_seq, Nullable<int> cheque_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_PaymentCheques> infoList = new List<M_FIM_PaymentCheques>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@pay_seq", pay_seq);
                dbManager.AddParameters(2, "@cheque_seq", cheque_seq);

                strSQL = "SELECT pc.pay_cheque_seq, pc.pay_seq, pc.cheque_seq, pc.amount"
                    + ", che.cheque_no, che.bank_acc_seq, acc.bank_acc_no, acc.bank_acc_name"
                    + ", che.pay_date, pay.ref_no as pay_ref_no, pay.pay_name"

                    + ", CONCAT(CAST(cb.cheque_book_no as varchar), ' / ', CAST(che.cheque_no as varchar)) AS cheque_name"
                    + ", che.cheque_book_seq, cb.cheque_book_no"
                    + ", case when pay.contact_seq is not null then c.NameTh"
                            + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                        + " end as payable_name"
                    + ", che.cheque_status"
                    + ", case che.cheque_status"
                        + " when '1' then 'ใช้งานได้'"
                        + " when '2' then 'จ่ายแล้ว'"
                        + " when '3' then 'ขึ้นเงินแล้ว'"
                        + " else 'ยกเลิกแล้ว'"
                        + " end as cheque_status_name"
                    + ", che.cheque_date"

                    + ", pc.record_status, rs.record_status_name"
                    + ", pc.created_by, pc.created_when, pc.updated_by, pc.updated_when"
                    + " FROM fim_payment_cheques pc"
                    + " LEFT JOIN fim_payments pay ON pc.pay_seq = pay.pay_seq"
                    + " LEFT JOIN fim_cheques che ON pc.cheque_seq = che.cheque_seq"
                    + " LEFT JOIN fim_cheque_books cb ON che.cheque_book_seq = cb.cheque_book_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON che.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON pay.contact_person_seq = cp.contact_person_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON pay.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " LEFT JOIN sys_record_status rs ON pay.record_status = rs.record_status"
                    + " WHERE pc.record_status not like @delete_status"
                    + (pay_seq != null ? " and pc.pay_seq = @pay_seq" : "")
                    + (cheque_seq != null ? " and pc.cheque_seq = @cheque_seq" : "");
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_PaymentCheques info = new M_FIM_PaymentCheques();
                        info.pay_cheque_seq = cn.ConvertNullInt(drc[i]["pay_cheque_seq"]);
                        info.pay_seq = cn.ConvertNullInt(drc[i]["pay_seq"]);
                        info.cheque_seq = cn.ConvertNullInt(drc[i]["cheque_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.cheque_book_seq = cn.ConvertNullInt(drc[i]["cheque_book_seq"]);
                        info.cheque_book_no = drc[i]["cheque_book_no"].ToString();
                        info.cheque_no = cn.ConvertNullInt(drc[i]["cheque_no"]);
                        info.bank_acc_seq = cn.ConvertNullInt(drc[i]["bank_acc_seq"]);
                        info.bank_acc_no = drc[i]["bank_acc_no"].ToString();
                        info.bank_acc_name = drc[i]["bank_acc_name"].ToString();
                        info.pay_date = cn.ConvertNullDateTime(drc[i]["pay_date"]);
                        info.pay_ref_no = drc[i]["pay_ref_no"].ToString();
                        info.pay_name = drc[i]["pay_name"].ToString();
                        info.payable_name = drc[i]["payable_name"].ToString();
                        info.cheque_date = cn.ConvertNullDateTime(drc[i]["cheque_date"]);

                        info.cheque_name = drc[i]["cheque_name"].ToString();
                        info.cheque_status = drc[i]["cheque_status"].ToString();
                        info.cheque_status_name = drc[i]["cheque_status_name"].ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_PaymentCheques> GetPaymentCheques(Nullable<int> pay_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_PaymentCheques> infoList = new List<M_FIM_PaymentCheques>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@pay_seq", pay_seq);


                strSQL = "SELECT pc.pay_cheque_seq, pc.pay_seq, che.cheque_seq, pc.amount"
                    + ", che.cheque_no, che.bank_acc_seq, acc.bank_acc_no, acc.bank_acc_name"
                    + ", case che.cheque_status"
                        + " when '1' then 'พร้อมใช้'"
                        + " when '2' then 'จ่ายแล้ว'"
                        + " when '3' then 'ขึ้นเงินแล้ว'"
                        + " else 'ยกเลิก'"
                        + " end as cheque_status_name"
                    + ", che.cheque_book_seq, cb.cheque_book_no"
                    + ", che.cheque_date"

                    + " FROM fim_cheques che"
                    + " LEFT JOIN fim_cheque_books cb ON che.cheque_book_seq = cb.cheque_book_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON che.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN fim_payment_cheques pc ON che.cheque_seq = pc.cheque_seq"
                        + " AND pc.record_status not like @delete_status"
                        + (pay_seq != null ? " AND pc.pay_seq = @pay_seq" : "")
                    + " WHERE che.record_status not like @delete_status"
                    + " AND (che.cheque_status = '1'"
                        + (pay_seq != null ? " OR pc.pay_seq = @pay_seq)" : ")");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_PaymentCheques info = new M_FIM_PaymentCheques();
                        info.pay_cheque_seq = cn.ConvertNullInt(drc[i]["pay_cheque_seq"]);
                        info.pay_seq = cn.ConvertNullInt(drc[i]["pay_seq"]);
                        info.cheque_seq = cn.ConvertNullInt(drc[i]["cheque_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.cheque_book_seq = cn.ConvertNullInt(drc[i]["cheque_book_seq"]);
                        info.cheque_book_no = drc[i]["cheque_book_no"].ToString();
                        info.cheque_no = cn.ConvertNullInt(drc[i]["cheque_no"]);
                        info.bank_acc_seq = cn.ConvertNullInt(drc[i]["bank_acc_seq"]);
                        info.bank_acc_no = drc[i]["bank_acc_no"].ToString();
                        info.bank_acc_name = drc[i]["bank_acc_name"].ToString();
                        info.cheque_status_name = drc[i]["cheque_status_name"].ToString();
                        info.cheque_date = cn.ConvertNullDateTime(drc[i]["cheque_date"]);
                        //info.pay_date = cn.ConvertNullDateTime(drc[i]["pay_date"]);
                        //info.pay_ref_no = drc[i]["pay_ref_no"].ToString();
                        //info.pay_name = drc[i]["pay_name"].ToString();

                        //info.record_status = drc[i]["record_status"].ToString();
                        //info.record_status_name = drc[i]["record_status_name"].ToString();
                        //info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        //info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        //info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        //info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        //public IList<M_FIM_PaymentCheques> GetList(Nullable<int> pay_seq, Nullable<int> cheque_seq)
        //{
        //    string strSQL;
        //    DataRowCollection drc = null;
        //    IList<M_FIM_PaymentCheques> infoList = new List<M_FIM_PaymentCheques>();
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(3);

        //        dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(1, "@pay_seq", pay_seq);
        //        dbManager.AddParameters(2, "@cheque_seq", cheque_seq);

        //        strSQL = "SELECT pay_cheque_seq, "
        //            + ", concat(coalesce(ref_no, ''), ' - ', coalesce(transfer_name, '')) as transfer_name"
        //            + " FROM fim_payment_cheques"
        //            + " WHERE record_status not like @delete_status";

        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        if (drc.Count > 0)
        //        {
        //            for (int i = 0; i < drc.Count; i++)
        //            {
        //                M_FIM_PaymentCheques info = new M_FIM_PaymentCheques();
        //                info.pay_seq = Convert.ToInt32(drc[i]["pay_seq"]);
        //                info.pay_name = drc[i]["pay_name"].ToString();
        //                infoList.Add(info);
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return infoList;
        //}

        public M_FIM_PaymentCheques GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_PaymentCheques info = new M_FIM_PaymentCheques();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT pc.pay_cheque_seq, pc.pay_seq, pc.cheque_seq, pc.amount"
                    + ", che.cheque_no, che.bank_acc_seq, acc.bank_acc_no, acc.bank_acc_name"
                    + ", che.pay_date, pay.ref_no as pay_ref_no, pay.pay_name"
                    + ", che.cheque_book_seq, cb.cheque_book_no"

                    + ", CONCAT(CAST(cb.cheque_book_no as varchar), ' / ', CAST(che.cheque_no as varchar)) AS cheque_name"
                    + ", case when pay.contact_seq is not null then c.NameTh"
                            + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                        + " end as payable_name"
                    + ", che.cheque_status"
                    + ", case che.cheque_status"
                        + " when '1' then 'ใช้งานได้'"
                        + " when '2' then 'จ่ายแล้ว'"
                        + " when '3' then 'ขึ้นเงินแล้ว'"
                        + " else 'ยกเลิกแล้ว'"
                        + " end as cheque_status_name"
                    + ", che.cheque_date"

                    + ", pc.record_status, rs.record_status_name"
                    + ", pc.created_by, pc.created_when, pc.updated_by, pc.updated_when"
                    + " FROM fim_payment_cheques pc"
                    + " LEFT JOIN fim_payments pay ON pc.pay_seq = pay.pay_seq"
                    + " LEFT JOIN fim_cheques che ON pc.cheque_seq = che.cheque_seq"
                    + " LEFT JOIN fim_cheque_books cb ON che.cheque_book_seq = cb.cheque_book_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON che.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON pay.contact_person_seq = cp.contact_person_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON pay.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    //+ " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    //+ " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN sys_record_status rs ON pay.record_status = rs.record_status"
                    + " WHERE pc.pay_cheque_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.pay_cheque_seq = cn.ConvertNullInt(drc[0]["pay_cheque_seq"]);
                    info.pay_seq = cn.ConvertNullInt(drc[0]["pay_seq"]);
                    info.cheque_seq = cn.ConvertNullInt(drc[0]["cheque_seq"]);
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.cheque_book_seq = cn.ConvertNullInt(drc[0]["cheque_book_seq"]);
                    info.cheque_book_no = drc[0]["cheque_book_no"].ToString();
                    info.cheque_no = cn.ConvertNullInt(drc[0]["cheque_no"]);
                    info.bank_acc_seq = cn.ConvertNullInt(drc[0]["bank_acc_seq"]);
                    info.bank_acc_no = drc[0]["bank_acc_no"].ToString();
                    info.bank_acc_name = drc[0]["bank_acc_name"].ToString();
                    info.pay_date = cn.ConvertNullDateTime(drc[0]["pay_date"]);
                    info.pay_ref_no = drc[0]["pay_ref_no"].ToString();
                    info.pay_name = drc[0]["pay_name"].ToString();
                    info.payable_name = drc[0]["payable_name"].ToString();
                   // info.cheque_status = drc[0]["cheque_status"].ToString();
                    info.cheque_status_name = drc[0]["cheque_status_name"].ToString();
                    info.cheque_name = drc[0]["cheque_name"].ToString();
                    info.cheque_date = cn.ConvertNullDateTime(drc[0]["cheque_date"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    info.record_status_name = drc[0]["record_status_name"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public void Insert(M_FIM_PaymentCheques info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@pay_cheque_seq", info.pay_cheque_seq);
                dbManager.AddParameters(1, "@pay_seq", info.pay_seq);
                dbManager.AddParameters(2, "@cheque_seq", info.cheque_seq);
                dbManager.AddParameters(3, "@amount", info.amount);

                dbManager.AddParameters(4, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(5, "@created_by", info.created_by);
                dbManager.AddParameters(6, "@created_when", info.created_when);

                strSQL = "INSERT INTO fim_payment_cheques"
                    + "(pay_seq, cheque_seq, amount"
                    + " , record_status, created_by, created_when)"
                    + " VALUES(@pay_seq, @cheque_seq, @amount"
                        + ", @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_PaymentCheques info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@pay_cheque_seq", info.pay_cheque_seq);
                dbManager.AddParameters(1, "@pay_seq", info.pay_seq);
                dbManager.AddParameters(2, "@cheque_seq", info.cheque_seq);
                dbManager.AddParameters(3, "@amount", info.amount);

                dbManager.AddParameters(4, "@updated_by", info.updated_by);
                dbManager.AddParameters(5, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_payment_cheques"
                    + " set pay_seq = @pay_seq"
                    + ", cheque_seq = @cheque_seq"
                    + ", amount = @amount"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE pay_cheque_seq = @pay_cheque_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_PaymentCheques info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@pay_cheque_seq", info.pay_cheque_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_payment_cheques"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE pay_cheque_seq = @pay_cheque_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string SumMoney(string strCheque)
        {
            string strSQL = "";
            string Money = "";

            dbManager.Open();
            dbManager.CreateParameters(2);

            dbManager.AddParameters(0, "@cheque_seq", strCheque);
            dbManager.AddParameters(1, "@delete_record_status", SystemInfo._DeleteRecordStatus);

            try
            {
                strSQL = "SELECT SUM(amount) FROM fim_payment_cheques "
                        + " WHERE cheque_seq = @cheque_seq AND record_status NOT LIKE @delete_record_status";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                Money = Convert.ToString(dtb.Rows[0][0]);
                return Money;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
